﻿Imports System.Data.SqlClient

Public Class frmProducersRegistryOld

    Sub FillProducersList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Producers Where FullName Like N'%" & _
                                      Me.txtProducerNameSearch.Text.Trim & "%' Order By SNo", cnn)
            Dim Reader As SqlDataReader

            Me.GridProducersSalesmen.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridProducersSalesmen.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("FullName"), Reader.Item("MobileNo"), _
                                                                Reader.Item("Email"), Reader.Item("IDInfo"), Reader.Item("Address"), _
                                                                "Edit", "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            If Me.txtProducerName.Text.Trim.Length = 0 Then
                MsgBox("Please fill in all mandatory fields")
                Exit Sub
            ElseIf Me.txtEmail.Text.Trim.Length > 0 And _
                   Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtEmail.Text.Trim, "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
                MsgBox("Please enter a valid email address or leave blank")
                Exit Sub
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Insert Into Producers " & _
                                          "(FullName,MobileNo,Tele,Email,IDInfo,Address,AccNo,AccName) Values " & _
                                          "(@FullName,@MobileNo,@Tele,@Email,@IDInfo,@Address,@AccNo,@AccName)", cnn)

                cnn.Open()

                'Add values
                cmd.Parameters.AddWithValue("@FullName", Me.txtProducerName.Text.Trim)
                cmd.Parameters.AddWithValue("@MobileNo", Me.txtMobile.Text.Trim)
                cmd.Parameters.AddWithValue("@Tele", Me.txtTele.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@IDInfo", Me.txtIDInfo.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@AccNo", Me.txtAccNo.Text.Trim)
                cmd.Parameters.AddWithValue("@AccName", Me.txtAccName.Text.Trim)

                cmd.ExecuteNonQuery()
                cnn.Close()

                MsgBox("Saved Successfully!")

                Clear()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtProducerName.Clear()
        Me.txtTele.Clear()
        Me.txtEmail.Clear()
        Me.txtIDInfo.Clear()
        Me.txtAddress.Clear()
        Me.txtProducerName.Focus()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Private Sub GridProducersSalesmen_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProducersSalesmen.CellClick
        If e.ColumnIndex = 6 Then
            Dim a As New frmProducersEdit
            a.SNo = Me.GridProducersSalesmen.CurrentRow.Cells(0).Value
            a.ShowDialog()
            FillProducersList()

        ElseIf e.ColumnIndex = 7 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From Producers Where SNo=" & _
                                              Me.GridProducersSalesmen.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillProducersList()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Clear()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        FillProducersList()
    End Sub

    Private Sub txtProducerNameSearch_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtProducerNameSearch.KeyUp
        If e.KeyCode = Keys.Enter Then
            FillProducersList()
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Producers", cnn)
            Dim das As New DataSet
            das.Clear()

            cnn.Open()
            dap.Fill(das, "ProducersSalesmen")
            cnn.Close()

            Dim rpt As New ProducersList

            rpt.SetDataSource(das)
            Me.CrystalReportViewer1.ReportSource = rpt
            Me.CrystalReportViewer1.RefreshReport()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Me.Close()
    End Sub

    Private Sub frmProducersRegistry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub
End Class